--====================================
--  Create database trigger template 
--====================================
USE [TaskConnect.mdf]
GO

IF EXISTS(
  SELECT *
    FROM sys.triggers
   WHERE name = N'FixMilestoneInNestedSetTrigger'
)
	DROP TRIGGER FixMilestoneInNestedSetTrigger
GO

CREATE TRIGGER FixMilestoneInNestedSetTrigger
            ON Children_Parent
         AFTER INSERT, DELETE, UPDATE
AS 
DECLARE @childid uniqueidentifier, @pid uniqueidentifier
DECLARE @upper INT, @lower INT

IF EXISTS (SELECT * FROM inserted)
BEGIN
    SELECT @childid = i.Children_ID, 
               @pid = i.Parent_ID,
             @upper = n.[Upper],
             @lower = n.[Lower]
      FROM inserted as i
      JOIN NestedSet n
        ON n.NodeId = i.Parent_ID
        
    UPDATE NestedSet SET [Upper] = [UPPER]+ 2 WHERE [UPPER] >= @upper
    UPDATE NestedSet SET [Lower] = [LOWER]+ 2 WHERE [LOWER] > @lower
    INSERT INTO NestedSet ([Lower], NodeId, [Upper])
         VALUES (@upper, @childid, @upper + 1)
END

IF EXISTS (SELECT * FROM deleted)
BEGIN
    SELECT @childid = d.Children_ID, 
               @pid = d.Parent_ID,
             @upper = n.[Upper],
             @lower = n.[Lower]
      FROM deleted as d
      JOIN NestedSet n
        ON n.NodeId = d.Parent_ID
        
    DELETE FROM NestedSet WHERE NodeId = @childid
END
GO


